﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.IO;
using LinqToExcel;
using LinqToExcel.Domain;
using PessModel;
using PessMvc.Helpers;
using System.Data;

namespace PessMvc.Controllers
{
    [Authorize]
    public class CandidateQuotaController : Controller
    {
        private PESSEntities db;

        public CandidateQuotaController()
        {
            db = new PESSEntities();
        }

        public ActionResult Quota(int activityID)
        {
            ViewData["StatusTable"] = Utility.Status(activityID,db);
            ViewBag.SubjectID = new SelectList(db.Subjects.Where(s => s.ActivityID == activityID).ToList(), "ID", "Name");
            ViewBag.CountyCode = new SelectList(db.Counties, "Code", "Name");

            return View(activityID);
        }
        // GET: /CandidateQuota/
        [HttpPost]
        public ActionResult AddQuota(HttpPostedFileBase file, int activityID)
        {
            // Verify that the user selected a file
            if (file != null && file.ContentLength > 0)
            {
                // extract only the fielname
                var fileName = Path.GetFileName(file.FileName);
                // store the file inside ~/App_Data/uploads folder
                var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);
                file.SaveAs(path);


                var excel = new ExcelQueryFactory(path);
                //excel.FileName = ;
                excel.DatabaseEngine = DatabaseEngine.Ace;
                var rows = (from r in excel.WorksheetNoHeader()//Worksheet()
                           select r).ToList();
                //Add subjects to activity
                for (int h = 1; h < rows[0].Count; h++)
                {                    
                    Subject subject = new Subject() {ActivityID=activityID,Name=rows[0][h].Value.ToString().Trim() };
                    if (db.Subjects.Where(s => s.Name == subject.Name && s.ActivityID == subject.ActivityID).Count() <= 0)//If subject under the Activity with the same new does not yet exist, then add it to db.
                    {
                        db.Subjects.Add(subject);
                    }
                }
                db.SaveChanges();
                //Add Quotas
                for (int i = 1; i < rows.Count; i++)
                {
                    for (int j = 1; j < rows[i].Count; j++)
                    {
                        //Sometimes excel file may contain blank value, though eyes cannot detect.
                        if (rows[i][0].Value.ToString().Trim() != "")
                        {
                            CandidateQuota quota = new CandidateQuota()
                            {
                                CountyCode = rows[i][0].Value.ToString().ToCountyCode(db),
                                SubjectID = rows[0][j].Value.ToString().ToSubjectID(activityID, db),
                                Quota = Convert.IsDBNull(rows[i][j].Value) ? 0 : Convert.ToInt32(rows[i][j].Value)
                            };
                            if (db.CandidateQuotas.Where(q => q.CountyCode == quota.CountyCode && q.SubjectID == quota.SubjectID).Count() <= 0)
                            {
                                db.CandidateQuotas.Add(quota);
                            }
                        }
                    }
                }
                db.SaveChanges();
            }

            return RedirectToAction("Quota", new {activityID=activityID }); 
        }

        [HttpPost]
        public ActionResult ChangeQuota(CandidateQuota model,int activityID)
        {
            if (ModelState.IsValid&&model.CountyCode!=null&&model.SubjectID>0&&model.Quota!=null)
            {
                db.Entry(model).State = EntityState.Modified;
                db.SaveChanges();
            }

            //int activityID =db.Subjects.Find(model.SubjectID).ActivityID;

            return RedirectToAction("Quota", new { activityID = activityID });

        }

        [HttpPost]
        public ActionResult DeleteQuota(int? subjectID, int activityID)
        {
            if (subjectID != null)
            {
                var subject = db.Subjects.Find(subjectID);
                //Remove the quotas that has no archives
                var quotas = db.CandidateQuotas.Where(q => q.SubjectID == subjectID);
                foreach (var q in quotas)
                {
                    if (db.Archives.Where(a => a.SubjectID == q.SubjectID && a.CountyCode == q.CountyCode).Count() == 0)
                    { db.CandidateQuotas.Remove(q); }
                }
                //Remove th subject if it has no Archives&Rules&ExpertGroups
                if (subject.Archives.Count == 0
                    &&subject.Rules.Count==0
                    &&subject.ExpertGroups.Count==0)
                {
                    db.Subjects.Remove(subject);
                }

                db.SaveChanges();
            }

            return RedirectToAction("Quota", new { activityID = activityID });
        }
    }
}
